package base;

import com.alibaba.fastjson.JSON;
import com.github.crab2died.ExcelUtils;
import com.pt.base.dto.MainDTO;
import com.pt.base.dto.MainOpenSearchDTO;
import com.pt.base.dto.MyContentDTO;
import com.pt.base.dto.OpenSearchDTO;

import modules.Student1;
import modules.Student2;
import modules.StudentScore;

import org.junit.Test;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

public class Excel2DB {
    public static boolean flag = true;
    @Test
    public void excel2Object() throws Exception {

        String path = "E:\\ideaWorkspace\\Excel4J\\src\\test\\resources\\students_01.xlsx";

        System.out.println("读取全部：");
        List<Student1> students = ExcelUtils.getInstance().readExcel2Objects(path, Student1.class);
        for (Student1 stu : students) {
            System.out.println(stu);
        }
        System.out.println("读取指定行数：");
        students = ExcelUtils.getInstance().readExcel2Objects(path, Student1.class, 0, 3, 0);
        for (Student1 stu : students) {
            System.out.println(stu);
        }
    }

    @Test
    public void excel2Object2() {

        String path = "E:\\ideaWorkspace\\Excel4J\\src\\test\\resources\\students_02.xlsx";
        try {

            // 1)
            // 不基于注解,将Excel内容读至List<List<String>>对象内
            List<List<String>> lists = ExcelUtils.getInstance().readExcel2List(path, 1, 2, 0);
            System.out.println("读取Excel至String数组：");
            for (List<String> list : lists) {
                System.out.println(list);
            }

            // 2)
            // 基于注解,将Excel内容读至List<Student2>对象内
            // 验证读取转换函数Student2ExpelConverter
            // 注解 `@ExcelField(title = "是否开除", order = 5, readConverter =  Student2ExpelConverter.class)`
            List<Student2> students = ExcelUtils.getInstance().readExcel2Objects(path, Student2.class, 0, 0);
            System.out.println("读取Excel至对象数组(支持类型转换)：");
            for (Student2 st : students) {
                System.out.println(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 基于注解,将Excel内容读至List<Student2>对象内
    // 验证读取转换函数Student2ExpelConverter，注解 `@ExcelField(title = "是否开除", order = 5, readConverter = Student2ExpelConverter.class)`
    @Test
    public void testReadConverter() throws Exception {

        String path = "E:\\ideaWorkspace\\Excel4J\\src\\test\\resources\\students_02.xlsx";
        List<Student2> students = ExcelUtils.getInstance().readExcel2Objects(path, Student2.class, 0, 0);
        System.out.println("读取Excel至对象数组(支持类型转换)：");
        for (Student2 st : students) {
            System.out.println(st);
        }
    }

    //测试读取带有公式的单元格，并返回公式的值
    @Test
    public void testReadExcel_XLS() throws  Exception{
        String path = "E:\\ideaWorkspace\\Excel4J\\src\\test\\resources\\StudentScore.xlsx";
        System.out.println(Paths.get(path).toUri().getPath());
        List<StudentScore> projectExcelModels = ExcelUtils.getInstance().readExcel2Objects(path, StudentScore.class);
        System.out.println(projectExcelModels);
    }

    @Test
    public void excelCG() throws  Exception{

        String path = "D:\\tmpExcel\\广东信基集团2018.04.xls";
        try {

            // 1)
            // 不基于注解,将Excel内容读至List<List<String>>对象内
            List<List<String>> lists = ExcelUtils.getInstance().readExcel2List(path, 2, 20, 0);
            System.out.println("====CG=读取Excel至String数组：");
            for (List<String> list : lists) {
                System.out.println(list);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testParseAll() throws Exception{
        int maxRows = 10000;
        String path = "F:\\验证类项目\\tmpExcel解析导入openSearch\\2\\";

        File directory = new File(path);
        File[] files = directory.listFiles();
        flag = true;
        //开始信息
        this.outSomeString(path,"[");
        for(int i = 0;i < files.length;i ++){
            File tmp = files[i];
            if(tmp.getName().endsWith("xls") || tmp.getName().endsWith(".xlsx")){
                try {
                    System.out.println("====CG=读取Excel至String数组："+tmp.getAbsolutePath());
                    for(int k =0;k < 30;k ++){
                        System.out.println("kkkkkkkkkkkkk"+k);
                        List<List<String>> lists = ExcelUtils.getInstance().readExcel2List(tmp.getAbsolutePath(), 2,maxRows , k);
                        if(lists != null && lists.size() >0){
                            this.outTxt(directory.getCanonicalPath(),tmp.getAbsolutePath(),lists,(i+1+k)*maxRows);
                        }else{
                            break;
                        }
                    }
                }catch(IllegalArgumentException e1){
                    System.out.println(e1.getMessage());
                    //e1.printStackTrace();
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        
        //补充最后信息
        this.outSomeString(path,"]");
    }

    private void outSomeString(String path, String someStr) throws Exception {
        FileWriter fw = null;
        try {
            fw = new FileWriter(path + "\\result2.txt",true);
            fw.write(someStr);
            if(fw != null){
                try {
                    fw.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    private void outTxt(String txtPath,String excelPath,List<List<String>> lists,int i){
        FileWriter fw = null;
        try {
            //加true表示在写在文件的结尾而不是开头
            fw = new FileWriter(txtPath + "\\result2.txt",true);
            //fw.write("+++++++++"+excelPath+"+++++++++\r\n");
            int k =0;
            for (List<String> list : lists) {
                k=k+1;
                /*MyContentDTO tableDTO = new MyContentDTO();
                tableDTO.setContent(list.toString());
                tableDTO.setUuid(String.valueOf(new Date().getTime()));
                tableDTO.setExcelPath(excelPath);*/
                MainDTO tableDTO = new MainDTO();
                tableDTO.setId(String.valueOf(i+k));
                tableDTO.setBody(list.toString());
                tableDTO.setCreate_timestamp(String.valueOf(new Date().getTime()));
                tableDTO.setAuthor(excelPath);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                tableDTO.setTag(sdf.format(new Date()));


                MainOpenSearchDTO dto = new MainOpenSearchDTO();
                dto.setCmd("ADD");
                dto.setFields(tableDTO);
                //fw.write(","+JSON.toJSONString(dto) + "\r\n");
                if(flag){
                    fw.write(JSON.toJSONString(dto));
                }else{
                    fw.write(","+JSON.toJSONString(dto));
                }
                flag = false;
                //fw.write(list + "\r\n");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(fw != null){
                try {
                    fw.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }


    @Test
    public void excelCG2() throws  Exception{

        String path = "D:\\tmpExcel\\广东信基集团2018.04.xls";
        try {

            // 1)
            // 不基于注解,将Excel内容读至List<List<String>>对象内
            List<List<String>> lists = ExcelUtils.getInstance().readExcel2ListCell2(path, 2,2, 20, 0);
            System.out.println("====excelCG2=读取Excel至String数组：");
            for (List<String> list : lists) {
                System.out.println(list);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
